/**
 * SQL执行计划解析服务
 * 支持MySQL、PostgreSQL和SQL Server的执行计划解析
 */

/**
 * 获取SQL查询执行计划
 * @param {string} connectionId 连接ID
 * @param {string} sql SQL语句
 * @param {Object} databaseService 数据库服务实例
 * @returns {Promise<Object>} 执行计划对象
 */
export async function getQueryPlan(connectionId, sql, databaseService) {
  try {
    // 获取数据库类型
    const connection = await databaseService.getConfig(connectionId)
    const dbType = connection.type.toLowerCase()
    
    // 根据数据库类型生成EXPLAIN语句
    const explainSql = generateExplainSQL(sql, dbType)
    
    // 执行EXPLAIN查询
    const result = await databaseService.executeQuery(connectionId, explainSql)
    
    // 如果有错误，直接返回错误信息
    if (result.error) {
      return {
        error: result.error,
        raw: result
      }
    }
    
    // 根据数据库类型解析执行计划
    return parsePlan(result, dbType, sql)
  } catch (error) {
    return {
      error: `获取执行计划失败: ${error.message}`,
      raw: null
    }
  }
}

/**
 * 生成EXPLAIN SQL语句
 * @param {string} sql 原始SQL语句
 * @param {string} dbType 数据库类型
 * @returns {string} EXPLAIN SQL语句
 */
function generateExplainSQL(sql, dbType) {
  sql = sql.trim()
  
  switch (dbType) {
    case 'mysql':
    case 'mariadb':
      // MySQL执行计划，使用FORMAT=JSON获取更详细的信息
      return `EXPLAIN FORMAT=JSON ${sql}`
      
    case 'postgresql':
      // PostgreSQL执行计划，使用JSON格式
      return `EXPLAIN (FORMAT JSON) ${sql}`
      
    case 'sqlserver':
      // SQL Server执行计划
      return `SET SHOWPLAN_ALL ON;\n${sql};\nSET SHOWPLAN_ALL OFF;`
      
    default:
      // 默认简单EXPLAIN
      return `EXPLAIN ${sql}`
  }
}

/**
 * 解析执行计划结果
 * @param {Object} result 执行结果
 * @param {string} dbType 数据库类型
 * @param {string} originalSql 原始SQL语句
 * @returns {Object} 解析后的执行计划
 */
function parsePlan(result, dbType, originalSql) {
  try {
    switch (dbType) {
      case 'mysql':
      case 'mariadb':
        return parseMySQLPlan(result)
        
      case 'postgresql':
        return parsePostgreSQLPlan(result)
        
      case 'sqlserver':
        return parseSQLServerPlan(result)
        
      default:
        return {
          error: `不支持的数据库类型: ${dbType}`,
          raw: result
        }
    }
  } catch (error) {
    return {
      error: `解析执行计划失败: ${error.message}`,
      raw: result
    }
  }
}

/**
 * 解析MySQL执行计划
 * @param {Object} result 执行结果
 * @returns {Object} 解析后的执行计划
 */
function parseMySQLPlan(result) {
  // MySQL的EXPLAIN FORMAT=JSON返回一个包含json字符串的单行单列结果
  if (!result.data || result.data.length === 0) {
    return { 
      error: '无法获取MySQL执行计划数据',
      raw: result
    }
  }
  
  // 获取JSON格式的计划数据
  const planData = result.data[0][result.columns[0].name]
  
  try {
    // 解析JSON字符串
    const planJson = typeof planData === 'string' ? JSON.parse(planData) : planData
    
    // 提取查询块
    const queryBlock = planJson.query_block
    
    // 构建树形执行计划
    const rootNode = parseMySQLQueryBlock(queryBlock)
    
    return {
      nodes: rootNode,
      totalCost: calculateTotalCost(rootNode),
      rows: rootNode.rows,
      time: null, // MySQL执行计划不提供估计时间
      raw: planJson
    }
  } catch (error) {
    return {
      error: `解析MySQL执行计划失败: ${error.message}`,
      raw: result
    }
  }
}

/**
 * 解析MySQL查询块
 * @param {Object} queryBlock MySQL查询块
 * @returns {Object} 解析后的节点
 */
function parseMySQLQueryBlock(queryBlock) {
  // 处理SELECT查询
  if (queryBlock.select_id) {
    const node = {
      operation: 'SELECT',
      type: 'SELECT',
      rows: queryBlock.rows_examined_per_scan || 0,
      cost: queryBlock.cost_info?.query_cost || 0,
      details: {
        select_id: queryBlock.select_id,
        ...queryBlock.cost_info
      },
      children: []
    }
    
    // 处理表访问方式
    if (queryBlock.table) {
      node.children.push(parseMySQLTable(queryBlock.table))
    }
    
    // 处理JOIN操作
    if (queryBlock.joined_tables) {
      const joinNode = parseMySQLJoinedTables(queryBlock.joined_tables)
      node.children.push(joinNode)
    }
    
    // 处理ORDER BY
    if (queryBlock.ordering_operation) {
      const orderNode = {
        operation: 'ORDER BY',
        type: 'ORDERING',
        rows: node.rows,
        cost: queryBlock.cost_info?.sort_cost || 0,
        details: queryBlock.ordering_operation,
        children: []
      }
      node.children.push(orderNode)
    }
    
    return node
  }
  
  // 如果不是标准查询块，返回通用节点
  return {
    operation: 'UNKNOWN',
    type: 'UNKNOWN',
    rows: 0,
    cost: 0,
    details: queryBlock,
    children: []
  }
}

/**
 * 解析MySQL表访问
 * @param {Object} table MySQL表信息
 * @returns {Object} 解析后的节点
 */
function parseMySQLTable(table) {
  const accessType = table.access_type || 'ALL'
  const tableName = table.table_name || 'Unknown'
  
  const node = {
    operation: `${accessType} (${tableName})`,
    type: accessType,
    rows: table.rows_examined_per_scan || 0,
    cost: table.cost_info?.read_cost || 0,
    filters: getFiltersFromMySQLTable(table),
    details: {
      table: tableName,
      access_type: accessType,
      possible_keys: table.possible_keys?.join(', '),
      key: table.key,
      key_length: table.key_length,
      ref: table.ref?.join(', '),
      ...table.cost_info
    },
    children: []
  }
  
  return node
}

/**
 * 从MySQL表信息中提取过滤条件
 * @param {Object} table MySQL表信息
 * @returns {string} 过滤条件
 */
function getFiltersFromMySQLTable(table) {
  const filters = []
  
  if (table.attached_condition) {
    filters.push(`WHERE: ${table.attached_condition}`)
  }
  
  return filters.join(', ')
}

/**
 * 解析MySQL JOIN操作
 * @param {Object} joinedTables MySQL JOIN表信息
 * @returns {Object} 解析后的节点
 */
function parseMySQLJoinedTables(joinedTables) {
  const node = {
    operation: 'JOIN',
    type: joinedTables.join_type || 'INNER JOIN',
    rows: joinedTables.rows_examined_per_scan || 0,
    cost: joinedTables.cost_info?.read_cost || 0,
    details: {
      ...joinedTables.cost_info
    },
    children: []
  }
  
  // 处理左表
  if (joinedTables.table) {
    node.children.push(parseMySQLTable(joinedTables.table))
  }
  
  // 处理右表
  if (joinedTables.tables) {
    for (const table of joinedTables.tables) {
      if (table.table) {
        node.children.push(parseMySQLTable(table.table))
      } else if (table.joined_tables) {
        node.children.push(parseMySQLJoinedTables(table.joined_tables))
      }
    }
  }
  
  return node
}

/**
 * 解析PostgreSQL执行计划
 * @param {Object} result 执行结果
 * @returns {Object} 解析后的执行计划
 */
function parsePostgreSQLPlan(result) {
  // PostgreSQL的EXPLAIN (FORMAT JSON)返回JSON数组字符串
  if (!result.data || result.data.length === 0) {
    return { 
      error: '无法获取PostgreSQL执行计划数据',
      raw: result
    }
  }
  
  try {
    // 获取JSON格式的计划数据
    const planData = result.data[0][result.columns[0].name]
    const planJson = typeof planData === 'string' ? JSON.parse(planData) : planData
    
    if (!planJson[0]?.Plan) {
      return {
        error: '无效的PostgreSQL执行计划格式',
        raw: planJson
      }
    }
    
    // 提取根计划节点
    const rootPlan = planJson[0].Plan
    const planStats = planJson[0]
    
    // 构建树形执行计划
    const rootNode = parsePostgreSQLPlanNode(rootPlan)
    
    return {
      nodes: rootNode,
      totalCost: rootPlan['Total Cost'] || 0,
      rows: rootPlan['Plan Rows'] || 0,
      time: planStats['Planning Time'] + planStats['Execution Time'] || null,
      raw: planJson
    }
  } catch (error) {
    return {
      error: `解析PostgreSQL执行计划失败: ${error.message}`,
      raw: result
    }
  }
}

/**
 * 解析PostgreSQL计划节点
 * @param {Object} planNode PostgreSQL计划节点
 * @returns {Object} 解析后的节点
 */
function parsePostgreSQLPlanNode(planNode) {
  const node = {
    operation: planNode['Node Type'] || 'Unknown',
    type: planNode['Node Type'] || 'Unknown',
    rows: planNode['Plan Rows'] || 0,
    cost: (planNode['Total Cost'] || 0) - (planNode['Startup Cost'] || 0),
    filters: getFiltersFromPostgreSQLNode(planNode),
    details: {
      'total_cost': planNode['Total Cost'],
      'startup_cost': planNode['Startup Cost'],
      'plan_rows': planNode['Plan Rows'],
      'plan_width': planNode['Plan Width'],
      'relation_name': planNode['Relation Name'],
      'schema': planNode['Schema'],
      'alias': planNode['Alias'],
      'join_type': planNode['Join Type'],
      'index_name': planNode['Index Name'],
      'sort_key': planNode['Sort Key']?.join(', '),
      'group_key': planNode['Group Key']?.join(', ')
    },
    children: []
  }
  
  // 处理子计划
  if (planNode.Plans && planNode.Plans.length > 0) {
    node.children = planNode.Plans.map(childPlan => parsePostgreSQLPlanNode(childPlan))
  }
  
  return node
}

/**
 * 从PostgreSQL节点中提取过滤条件
 * @param {Object} planNode PostgreSQL计划节点
 * @returns {string} 过滤条件
 */
function getFiltersFromPostgreSQLNode(planNode) {
  const filters = []
  
  if (planNode['Filter']) {
    filters.push(`Filter: ${planNode['Filter']}`)
  }
  
  if (planNode['Join Filter']) {
    filters.push(`Join Filter: ${planNode['Join Filter']}`)
  }
  
  if (planNode['Index Cond']) {
    filters.push(`Index Cond: ${planNode['Index Cond']}`)
  }
  
  if (planNode['Recheck Cond']) {
    filters.push(`Recheck Cond: ${planNode['Recheck Cond']}`)
  }
  
  return filters.join(', ')
}

/**
 * 解析SQL Server执行计划
 * @param {Object} result 执行结果
 * @returns {Object} 解析后的执行计划
 */
function parseSQLServerPlan(result) {
  // SQL Server的SET SHOWPLAN_ALL返回多行结果集
  if (!result.data || result.data.length === 0) {
    return { 
      error: '无法获取SQL Server执行计划数据',
      raw: result 
    }
  }
  
  try {
    // 转换结果为树形结构
    const rootNode = buildSQLServerPlanTree(result.data)
    
    return {
      nodes: rootNode,
      totalCost: calculateTotalCost(rootNode),
      rows: rootNode.rows,
      time: null, // SQL Server执行计划不提供估计时间
      raw: result.data
    }
  } catch (error) {
    return {
      error: `解析SQL Server执行计划失败: ${error.message}`,
      raw: result
    }
  }
}

/**
 * 构建SQL Server执行计划树
 * @param {Array} planRows 执行计划行数据
 * @returns {Object} 执行计划树
 */
function buildSQLServerPlanTree(planRows) {
  // 根节点
  const rootNode = {
    operation: 'SQL Statement',
    type: 'ROOT',
    rows: 0,
    cost: 0,
    details: {},
    children: []
  }
  
  // 处理每一行
  let currentNode = rootNode
  const nodeStack = [rootNode]
  
  for (const row of planRows) {
    const nodeType = row['StmtText'] || row['PhysicalOp'] || 'Unknown'
    const estRows = parseFloat(row['EstimateRows'] || 0)
    const estIO = parseFloat(row['EstimateIO'] || 0)
    const estCPU = parseFloat(row['EstimateCPU'] || 0)
    const cost = estIO + estCPU
    
    // 解析节点级别（通过NodeId的格式判断）
    const nodeId = row['NodeId'] || ''
    const level = nodeId.split('.').length - 1
    
    // 创建新节点
    const node = {
      operation: nodeType,
      type: row['PhysicalOp'] || 'OPERATION',
      rows: estRows,
      cost: cost,
      filters: row['Predicate'] || '',
      details: {
        'logical_op': row['LogicalOp'],
        'estimate_rows': estRows,
        'estimate_io': estIO,
        'estimate_cpu': estCPU,
        'node_id': nodeId,
        'predicate': row['Predicate'],
        'object': row['Object']
      },
      children: []
    }
    
    // 根据级别调整当前节点
    if (level === 0) {
      // 顶层节点直接加到根节点
      rootNode.children.push(node)
      nodeStack.length = 1 // 重置堆栈
      nodeStack.push(node)
      currentNode = node
    } else if (level > nodeStack.length - 1) {
      // 下一级节点
      currentNode.children.push(node)
      nodeStack.push(node)
      currentNode = node
    } else {
      // 回溯到正确的级别
      nodeStack.length = level + 1
      currentNode = nodeStack[level]
      currentNode.children.push(node)
      nodeStack.push(node)
      currentNode = node
    }
    
    // 累加根节点的行数和成本
    rootNode.rows = Math.max(rootNode.rows, estRows)
    rootNode.cost += cost
  }
  
  return rootNode
}

/**
 * 计算执行计划总成本
 * @param {Object} node 执行计划节点
 * @returns {number} 总成本
 */
function calculateTotalCost(node) {
  let cost = node.cost || 0
  
  if (node.children && node.children.length > 0) {
    for (const child of node.children) {
      cost += calculateTotalCost(child)
    }
  }
  
  return cost
} 